# -*- coding: utf-8 -*-

# modulo de operações sobre uma base de dados sqlite3
# demostra um teste na criacao
# de base de dados
# IPBeja - Escola Superior de Tecnologia e de Getão
# Engenharia Informática
# Elisio Goncalves Ribeiro nº4708

import sqlite3
import re


##
################################# OPERACOES DECRIACAO #########################
##

##
# metodo que cria uma ligaçao a um ficheiro sqlite no disco
# pde ser ficheiro jaexistente ou cria um novo no endereco indicado
def sql_ligar_a_bd(endereco_bd):
    '''
    Ligar ao endereco fonecido e retornar uma 
    ligação a base de dados criada
        endereco_bd -> caminho ou nome do ficheiro a ligar ou criar
    '''
    try:
        endereco_bd = endereco_bd.encode("utf8")        
        # conexao/criacao no disco da Base de Dados
        bd_con = sqlite3.connect(endereco_bd)
        
    except:
        return ("## AVISO! ##\n\t Verifiquue o endereço da Base de Dados!",)
    else:
        return bd_con,endereco_bd                                              #retorna uma ligação a bd
    pass
pass

##
# metodo exclusivamente para confirmar a escrita dos 
# dadosno disco e fechar a ligaçãoo à base de dados
def sql_guardar_desligar_bd(bd_con, cursor):
    '''
    recebe um cursor e uma ligaçao e
    fechar e as ligações e fazer um ultimo commit aos dados escritos
    '''
    try:
        cursor.close()
        bd_con.commit()
        bd_con.close()
    except:
        return
    pass
pass

##
#meto para criar as instruçoes de preenchimentos das tabelas
def sql_criar_insert_query(nome_tabela, nms_coluna):
    '''
    recebe um string  para nome da tabela e uma lista com  os atributos 
    que sao valores para as colunas da tabela
    
        nome_tabela - string que vai ser a dinominação da tabela
        nms_coluna - os atributos para as colunas da tabela
        retun 'sql_insert' - instruçãoo preformatada de prenchimeno da tabela
    '''
    sql_value = ' VALUES('
    nm_coluna = []
    for valor in nms_coluna:
        nm_coluna.append(valor[0])
        sql_value += '?,'
    pass
    nm_coluna = tuple(nm_coluna)
    sql_insert = 'INSERT INTO {0} {1}'.format(nome_tabela, nm_coluna)
    
    sql_value = sql_value[:sql_value.rfind(',')]
    sql_value += ')'
        
    sql_insert = sql_insert + (sql_value)
    return sql_insert
pass

##
# criação de tabelas apartir dos nomes das folhas
# e dos valores das colunas
def sql_criar_tabelas(cursor, nome_tabela, atributos):
    '''
    metodo para criar tabelas baseando dos parametros passados
    nome_tabela - nome da tabela a ser criada
    atributos - lista de tuplos no formato (campo, tipo)
    '''
    sql_cr = 'CREATE TABLE IF NOT EXISTS {0} (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, '.format(nome_tabela)
    for valor in atributos:

        sql_cr += valor[0] +' '+ valor[1] + ','                                 #formata a sql_stment com os atributos
    pass
    sql_cr = sql_cr[:sql_cr.rfind(',')]
    sql_cr += ')'
    try:
        cursor.execute(sql_cr)                                                  #executa a sql_stment criada para criação da tabela
    except: pass
pass

##
#metodo para prencher as tabelas
def sql_preencher_tabelas(cursor, lst_dados, nome_tabela, nms_coluna):
    '''
    recebe uma lista de dados a guardar  a respectiva tabela onde guardar
    cria as querys de preenchimento e executa-as
        cursor - para operações sobre a BDados
        lst_dados - lista dos  dados a guardar
        nome_tabela - nome da tebela
        nms_coluna - os campos para s colunas da tabela
    '''
    sql_stmt = sql_criar_insert_query(nome_tabela, nms_coluna)
    try:
        cursor.executemany(sql_stmt, lst_dados)
    except: pass
pass

###############################################################################
################################## OPERAOES E CONSUTA #########################
##
#metodo que retorna uma lista de cursos dado um nome
def sql_curso_por_nome(db_nome, nm_curso1, nm_curso2=''):
    '''recebe um nome de curso ou parte do nome
    retorna uma lista de cursos com nomes indicados por
    estabelecimento, unidade e ano em que tiveram inscrições
        
        db_nome - nome da base de dados onde pesquisar
        nm_curso1 - um nome ou parte dele
        nm_curso2 - outra palavra do nome
        return lst_resultado - lista com os valores obtidos 
    '''
    lst_resultado =[]
    if (nm_curso2 != ''):
        sql_select_query = '''SELECT Curso_Superior, 
                        Estabelecimento_de_Ensino, 
                        Unidade_Organica, 
                        Ano 
                        FROM Tabela_4_1
                        WHERE Curso_Superior LIKE '%{0}%{1}' 
                        AND (h+m)>0
                        '''.format(nm_curso1, nm_curso2)
    else:
        sql_select_query = '''SELECT Curso_Superior, 
                        Estabelecimento_de_Ensino, 
                        Unidade_Organica, 
                        Ano 
                        FROM Tabela_4_1
                        WHERE Curso_Superior LIKE '%{0}%' 
                        AND (h+m)>0
                        '''.format(nm_curso1)
    pass
    try:
        bc = sql_ligar_a_bd(db_nome)[0]
        cursor = bc.cursor()
        cursor.execute(sql_select_query)
        nms_colunas = tuple( [descr[0] for descr in cursor.description])
        lst_resultado = cursor.execute(sql_select_query).fetchall()
        
        cursor.close()
        bc.close()
    except sqlite3.OperationalError: 
        pass
    else:
        return nms_colunas,lst_resultado
    pass
pass

##
################################## OPERAOES E CONSUTA #########################
##
#retorna uma lista de alunos inscritos nos cursos por ano
def sql_inscritos_por_curso(db_nome, nome_curso):
    '''recebe uma string de nome de curso e pesquisa por 
    incrições nesse curso retornando uma lista de inscritos por ano
        
        db_nome - nome da base de dados onde pesquisar
        nome_curso - string com o curso a pesquisar
        return lst_resultado - lista com os valores obtidos 
    '''
    lst_resultado =[]
    
    sql_select_query = '''SELECT DISTINCT curso_superior, 
                    (h+m) AS TAlunos, 
                    ano 
                    FROM tabela_4_1
                    WHERE curso_superior='{0}' 
                    AND TAlunos>0
                    ORDER BY ano '''.format(nome_curso)
    try:
        bc = sql_ligar_a_bd(db_nome)[0]
        cursor = bc.cursor()
        nms_colunas = tuple( [descr[0] for descr in cursor.description])
        lst_resultado = cursor.execute(sql_select_query).fetchall()
        
        print lst_resultado
        cursor.close()
        bc.close()
    except sqlite3.OperationalError: 
        pass
    else:
        return nms_colunas,lst_resultado
    pass
pass

##
################################## OPERAOES E CONSUTA #########################
##
#retorna a lista total de cursos por nivel por ano
def sql_cursos_por_nivel(db_nome, valor_ano=0):
    '''pesquisa e retorna uma lista de total de cursos por
    niveis de formação por cada ano. Se o ano for indicado, 
    retorna os valores  para esse ano, caso contrario 
    retorna para todos os anos
        
        db_nome - nome da base de dados onde pesquisar
        nome_curso - string com o curso a pesquisar
        return lst_resultado - lista com os valores obtidos 
    '''
    lst_resultado =[]
    if (valor_ano > 0):
        sql_select_query = '''SELECT ano, count(DISTINCT Curso_Superior) AS TCursos, 
                    nivel_de_formacao
                    FROM tabela_4_1
                    WHERE ano='{0}%' AND(h+m)>0
                    GROUP BY Nivel_de_formacao'''.format(nome_curso)
    else:
        sql_select_query = '''SELECT DISTINCT nivel_de_formacao AS nivel,
                    count(curso_superior) as TCursos, 
                    ano
                    FROM tabela_4_1 
                    WHERE (h+m)>0
                    GROUP BY ano, nivel_de_formacao'''
    pass
    try:
        bc = sql_ligar_a_bd(db_nome)[0]
        cursor = bc.cursor()
        nms_colunas = tuple( [descr[0] for descr in cursor.description])
        lst_resultado = cursor.execute(sql_select_query).fetchall()
        
        print lst_resultado
        cursor.close()
        bc.close()
    except sqlite3.OperationalError: 
        pass
    else:
        return nms_colunas,lst_resultado
    pass
pass

##
################################## OPERAOES E CONSUTA #########################
##
#retorna uma lista de alunos inscritos por nivel por ano
def sql_inscritos_por_nivel(db_nome):
    '''retorna uma lista de total de alunos inscritos por 
    nivel de formação e por ano
    
        db_nome - nome da base de dados onde pesquisar
        return lst_resultado - lista com os valores obtidos 
    '''
    lst_resultado =[]
    
    sql_select_query = '''SELECT DISTINCT nivel_de_formacao AS nivel,
                    sum(valor) AS TAlunos, 
                    ano
                    FROM tabela_1_4 
                    WHERE valor>0.0
                    GROUP BY ano, nivel_de_formacao'''
    try:
        bc = sql_ligar_a_bd(db_nome)[0]
        cursor = bc.cursor()
        nms_colunas = tuple( [descr[0] for descr in cursor.description])
        lst_resultado = cursor.execute(sql_select_query).fetchall()

        print lst_resultado
        cursor.close()
        bc.close()
    except sqlite3.OperationalError: 
        pass
    else:
        return nms_colunas,lst_resultado
    pass
pass


##
##################################
##
#retorna uma lista de alunos inscritos por nivel por ano
def sql_obter_lista_cursos(db_nome):
    '''retorna uma lista de total de alunos inscritos por 
    nivel de formação e por ano
    
        db_nome - nome da base de dados onde pesquisar
        return lst_resultado - lista com os valores obtidos 
    '''
    lst_resultado =[]
    
    sql_select_query = '''SELECT DISTINCT curso_superior AS Cursos,
                    FROM tabela_4_1 
                    WHERE valor>0.0
                    ORDER BY curso_superior'''
    try:
        bc = sql_ligar_a_bd(db_nome)[0]
        cursor = bc.cursor()
        nms_colunas = tuple( [descr[0] for descr in cursor.description])
        lst_resultado = cursor.execute(sql_select_query).fetchall()
        
        print lst_resultado
        cursor.close()
        bc.close()
    except sqlite3.OperationalError: 
        pass
    else:
        return nms_colunas,lst_resultado
    pass
pass

##
##################################
##
#retorna uma lista de alunos inscritos por nivel por ano
def sql_obter_lista_nivel(db_nome):
    '''retorna uma lista de total de alunos inscritos por 
    nivel de formação e por ano
    
        db_nome - nome da base de dados onde pesquisar
        return lst_resultado - lista com os valores obtidos 
    '''
    lst_resultado =[]
    
    sql_select_query = '''SELECT DISTINCT nivel_de_formacao AS nivel,
                    FROM tabela_1_4
                    ORDER BY nivel_de_formacao'''
    try:
        bc = sql_ligar_a_bd(db_nome)[0]
        cursor = bc.cursor()
        nms_colunas = tuple( cursor.description[0])
        lst_resultado = cursor.execute(sql_select_query).fetchall()
        
        print lst_resultado
        cursor.close()
        bc.close()
    except sqlite3.OperationalError: 
        pass
    else:
        return nms_colunas,lst_resultado
    pass
pass

##
##################################
##
#retorna uma lista das diferentes areas
def sql_obter_lista_area(db_nome):
    '''retorna uma lista de total de areas de educacao e formacao
    
        db_nome - nome da base de dados onde pesquisar
        return lst_resultado - lista com os valores obtidos 
    '''
    lst_resultado =[]
    
    sql_select_query = '''SELECT DISTINCT area_de_educacao_e_formacao
                    FROM tabela_4_1
                    ORDER BY area_de_educacao_e_formacao'''
    try:
        bc = sql_ligar_a_bd(db_nome)[0]
        cursor = bc.cursor()
        nms_colunas = tuple( [descr[0] for descr in cursor.description])
        lst_resultado = cursor.execute(sql_select_query).fetchall()
        
        print lst_resultado
        cursor.close()
        bc.close()
    except sqlite3.OperationalError: 
        pass
    else:
        return nms_colunas,lst_resultado
    pass
pass
